const connection = require('../app/database')

class CheckService {
  async getChecksBySportId(sportName='', pagenum = '1', pagesize = '10') {
    const statement = `		
      SELECT (SELECT COUNT(*) FROM 
      (SELECT c.id,c.state,s.sport_name,a.athlete_name FROM checks c JOIN sports s 
      ON c.sport_id = s.sport_id JOIN athletes a 
      ON a.athlete_id = c.athlete_id
      WHERE s.sport_name LIKE '%${sportName}%'
      LIMIT ?,?) p) totalCount,
      JSON_ARRAYAGG(JSON_OBJECT('id',u.id,'status',u.state,'sportName',u.sport_name,'athleteName',u.athlete_name)) list
      FROM	(SELECT c.id,c.state,s.sport_name,a.athlete_name FROM checks	c JOIN sports s 
      ON c.sport_id = s.sport_id JOIN athletes a 
      ON a.athlete_id = c.athlete_id
      WHERE s.sport_name LIKE '%${sportName}%'
      LIMIT ?,?) u;	
    `
    const offset = (pagenum - 1) * pagesize
    const [res] = await connection.execute(statement,[offset+"",pagesize,offset+"",pagesize])
    return res[0]
  }
  async checkItem(id) {
    const statement = `
      UPDATE checks SET state = 1
      WHERE id = ?;
    `
    const res = await connection.execute(statement,[id])
    return res
  }
}

module.exports = new CheckService()